//
// Database.h
// libprotea
// Tools Library for the Protea Project / Database Handler
// Copyright (C) 2003, 2004, 2005 eXegeSys, Inc.
// Copyright (C) 2008 Bruce A. James
//
// This program is part of the Protea Project.
//
// The Protea Project is free software; you can redistribute it and/or modify it 
// under the terms of the GNU General Public License as published by the 
// Free Software Foundation, either version 3 of the License, or (at your 
// option) any later version.
//
// The Protea Project is distributed in the hope that it will be useful, but 
// WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
// or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License 
// for more details.
// 
// You should have received a copy of the GNU General Public License along 
// with this program.  If not, see <http://www.gnu.org/licenses/>.
// 
// Please send correspondence to:
// 
// theproteaproject@gmail.com
//

#ifndef __PROTEA_DATABASE_H__
#define __PROTEA_DATABASE_H__

/**
	\file Database.h
	\brief Tools Database functions
	
	XCDBHandler class definition
	-	This class is a definition of the database handler for Protea

	XCTxn class definition
	-	This class is a definition of the transaction object for application programming

	XCDBHandlerConnection class definition
	-	This class is a definition of the connection object for all databases
*/


// local includes
#include "../Buffer/Buffer.h"
#include "../Fields/fields.h"
#include "../Appdic/appdic.h"
#include "../AppHandler/AppInterface.h"
#include "../OSDep/shlib.h"

#define	MAX_SQL_LEN	32768

// turn transaction validation on/off
#define TXNVALID	32768

// Additional data structures
////////////////////////////////////////////////////////////////////////////////////

// forward declare the database interface classes
class XCDBInterface;
class XCDBConnection;
class XCDBTransaction;
class XCDBStatement;
class XCSQLStatement;
class XCPrepSQLGen;

/// SQL statement types for MakeFull
enum sql_stmt_type {
	SQL_STMT_NONE = -1,
	/// Select
	SQL_STMT_SELECT = 0,
	/// Insert
	SQL_STMT_INSERT = 1,
	/// Update
	SQL_STMT_UPDATE = 2,
	/// Delete
	SQL_STMT_DELETE = 3,
	/// Count (A type of select)
	SQL_STMT_COUNT = 4,
	// The following are only used in prep, needed here for completeness
	SQL_STMT_CREATE = 5, 
	SQL_STMT_DROP = 6,
	SQL_STMT_ALTER = 7,
	SQL_STMT_RENAME = 8,
	SQL_STMT_FINALIZE = 9
};



/// SQL Parameters
enum exec_parm {
	/// No parameters - default
	SQL_NO_PARM = 0,
	/// Send back no results
	SQL_NO_RESULT = 1
};


/// Transaction Isolation Level Enumeration
enum isolevel
{
	/// Read Uncommitted
	read_uncommitted,
	/// Read Committed
	read_committed,
	/// Repeatable Read
	repeatable_read,
	/// Serializable
	serializable
};

/// Enumeration of supported database types
// The commercial version supported all of these, only the MySQL driver was
// released as Open Source -- TODO: Create drivers for other database types.
enum db_type
{
	db_none = -1,
	db_interbase = 0,
	db_mssql7 = 1,
	db_db2 = 2,
	db_oracle = 3,
	db_odbc = 4,
	db_access = 5,
	db_mysql = 6,      // only current supported database type
	db_sybase = 7
};

/// Enumeration of supported logic comparisons
enum logic_oper
{
	logic_and, 
	logic_or
};

/// Enumeration of supported comparison types
enum cmp_type
{
	cmp_equals,				// =
	cmp_notequals,			// <>
	cmp_lessthan,			// <
	cmp_greaterthan,		// >
	cmp_lessthanequals,		// <=
	cmp_greaterthanequals,	// >=
	cmp_between,			// BETWEEN
	cmp_like				// LIKE
};

/// Enumeration of valid order types
enum order_type
{
	order_asc, 
	order_desc
};

/// Enumeration of valid join types
enum join_type
{
	join_inner, 
	join_left, 
	join_right, 
	join_full
};

extern const char *logical_opers[];
extern const char *cmp_types[];
extern const char *order_types[];
extern const char *join_types[];

// class for db error mapping
//////////////////////////////////////////////////////////////////////////////
class __EXPORT XCDBErrorMap
{
public:
      XCDBErrorMap( long xcerrormsg, long dberrormsg );

	  long xcerror;
      long dberror;
      XCDBErrorMap* next;
};

// XCDBDriverDescr Class - Database Driver Descriptor
//////////////////////////////////////////////////////////////////////////////
class __EXPORT XCDBDriverDescr : public XCBase
{
public:
    XCDBDriverDescr();
    ~XCDBDriverDescr();

	db_type type;

	long GetError( long dberr );
    void AddError( long xcerr, long dberr );

	long size;
	long version;

	bool can_two_phase_commit;
	bool can_update_defaults;
	bool can_exclusive_access;
	bool can_drop_fields;
	bool datetimeonly;
	bool supports_table_rename;
	bool uses_default_constraint;
	bool requires_finalization;
	bool requires_manual_table_drop;	// Database won't roll back table creation commands

	long varchar_max_size;
	SINT64 blob_max_size;

private:
    XCDBErrorMap* errormap;
};

//////////////////////////////////////////////////////////////////////////////
/**
	\brief XCCompare Class ( for WHERE clauses )

	The XCCompare class allows a programmer to supply comparison information.

	\param logical Logical comparison used for the where clause, valid values are AND (default) and OR
	\param comparison Comparison to use for the where clause, for example = (default), <, >, <>, LIKE
	\param preparen Number of prentheses to pre-pend
	\param postparen Number of prentheses to post-pend
*/
class __EXPORT XCCompare : public XCBase 
{
public:
	/// Constructor
	XCCompare();

	/// Copy constructor
	XCCompare( const XCCompare& new_compare );

	/// Logical operator for this object
	logic_oper logical;

	/// Comparison operator for this object
	cmp_type comparison;

	/// format number
	long format;

	/// Number of pre-pended parentheses
	char preparen;

	/// Number of post-pended parentheses
	char postparen;

	/// Field that we are comparing
	XCField* field;

	/// Defaults to NULL, if not null, will be what field is compared against
	XCField* field2;
};

//////////////////////////////////////////////////////////////////////////////

/// XCOrder Class ( for ORDER BY clauses )
class __EXPORT XCOrder : public XCBase
{
public:
	/// Constructor
	XCOrder();

	/// Sort order
	order_type direction;

	/// The field to order
	XCField* field;

	/// format number
	XCBuffer* format;
};

/// XCJoin Class ( for JOIN operations )
class __EXPORT XCJoin : public XCBase
{
public:
	/// Constuctor
	XCJoin();

	/// Type of join
	join_type type;

	/// Buffer that we are joining
	XCBuffer* buffer;

	/// The predicate to be used
	XCCompare* predicate;

	/// The number of predicates in the array
	long predicate_count;
};

// XCDBConnection Class
///////////////////////////////////////////////////////////////////////////////////


class __EXPORT XCDBHandlerConnection : public XCBase
{
public:
	// Database interface
	XCDBInterface* dbi;

	// Database connection for this thread
	XCDBConnection* connection;

	// Ptr to application interface
	XCAppInterface* app;

	// Database definition record
	buffer_db_defn* defns;

	// Dynamic library
	XCSharedLibrary dl;
};



// XCSQLGen Class
//////////////////////////////////////////////////////////////////////////////////

/**
	\brief SQL Generator class

	Collects information about SQL statements such as SELECT, INSERT, UPDATE, and DELETE

*/
class __EXPORT XCSQLGen : public XCBase
{
public:
	friend class XCTxn;
	friend class XCReload;
	friend class XCSQLStatement;
	friend class XCDBStatement;

	/**
		\brief Constructor
	*/
	XCSQLGen( XCAppInterface* new_app );

	/**
		\brief Constructor
	*/
	XCSQLGen();

	/**
		\brief Constructor
	*/
	XCSQLGen( const XCSQLGen &new_SQLGen );

	~XCSQLGen();

	/**
		\brief Constructor

		Allows for quick generation of SQL statements
	*/
	XCSQLGen( sql_stmt_type sqltype, XCBuffer* buf, XCAppInterface* new_app );

	/**
		\brief Clear out current contents
	*/
	void Clear();

	/**
		\brief Generate an sql statement, using the defined primary keys
		
		\param sqltype Type of statement
		\param buffer Buffer to submit
		\param compare Only used for UPDATE.  Contains the original data, so that an optimized update can be issued.

		Allows for quick generation of SQL statements based on the key values
	*/
	void MakeFull( sql_stmt_type sqltype, XCBuffer* buffer, XCBuffer* compare = NULL );

	/** \brief Limit the number of rows to return in a SELECT sql statement (MS SQL Server ONLY).
		\param numrows Number of rows to return, where 0 = no limit
	*/
	void SetSelectLimit( long numrows );

	/**
		\brief Create a SELECT sql statement.
		\param buffer Source buffer
		\param fieldcount Number of fields to select
		\param ... Fields to select

		Expects XCField* values in the parameters.
	*/
	void MakeSELECT( XCBuffer* buffer, int fieldcount = 0, ... );

	/**
		\brief Create a SELECT sql statement.
		\param buffer Source buffer
		\param fieldcount Number of fields to select
		\param ... Field numbers to select

		Expects field numbers as long int in the parameters.

		WARNING: Using this function can break customizations.  It prevents
		users from modifying buffers, and the MakeSELECTExchange function
		should be used instead.
	*/
	void MakeSELECTByNum( XCBuffer* buffer, int fieldcount = 0, ... );

	/**
		\brief Create a SELECT sql statement.
		\param buffer Source buffer
		\param buffer Exchange buffer

		Makes a SELECT, but only includes those fields which match between the source
		and exchange buffers.
	*/
	void MakeSELECTExchange( XCBuffer* buffer, XCBuffer* exchange );

	/**
		\brief Create a SELECT DISTINCT sql statement
		\param buffer Source buffer
		\param fieldcount Number of fields to select distinct
		\param ... Field numbers
	*/
	void MakeSELECTDISTINCT( XCBuffer* buffer, int fieldcount = 0, ... );

	/**
		\brief Create an INSERT insert sql statement
		\param buffer Source buffer
	*/
	void MakeINSERT( XCBuffer* buffer );

	/**
		\brief Create an UPDATE sql statement
		\param buffer Input buffer
		\param changes Change buffer

		Takes a buffer that will contain the new values for the record and an optional changes
		buffer that is used to determine what fields actually changed to optimize the statement.
		If no changes buffer is supplied, all fields will be included in the update.
	*/
	void MakeUPDATE( XCBuffer* buffer, XCBuffer* changes = NULL );

	/**
		\brief Create a DELETE sql statement
		\param buffer Source buffer
	*/
	void MakeDELETE( XCBuffer* buffer );

	/// Makes an existing select statement distinct
	void AddDISTINCT();

	/**
		\brief Returns a 'row locking' select statement
		\param buffer Source buffer
		\param count Number of fields to lock on
		\param ... Field list, as longs

		Takes at least one field number and makes a sql statement of the form
		UPDATE [table] SET [field1]=[field1] WHERE [field2]=[field2.value], ...
	*/
	void MakeROWLOCK( XCBuffer* buffer, int count, ... );

	/**
		\brief Returns a 'row locking' select statement
		\param buffer Source buffer
		\param fieldlist Zero terminated array of field numbers to lock on

		Takes at least one field number and makes a sql statement of the form
		UPDATE [table] SET [field1]=[field1] WHERE [field2]=[field2.value], ...
	*/
	void MakeROWLOCK( XCBuffer* buf, long* fieldlist );

	/** 
		\brief Makes a count sql statement and returns a buffer that the count will be placed in
		\param buffer Source buffer
		\param field Specified field to count
		\param distinct Count distinct records, yes/no

		Create a dynamic buffer with one field that is a long that the count is placed into after the
		statement is executed.  
		
		Example: (count all customers with a distinct ID)
			XCBuffer* CountBuffer = query->MakeCOUNT( Customer, Customer->GetField( FLD_CUSTOMER_ID ), true );
			txnvars.dbtxn->ExecuteSQL( query, SQL_NO_RESULT );
			long CustomerCount = CountBuffer->GetFieldByIndex(0)->GetLong();
	*/
	XCBuffer* MakeCOUNT( XCBuffer* buffer, XCField* field = NULL, bool distinct = false );

	/**
		\brief Add a where clause to an sql statement given any number of XCField objects
		\param parmcount Number of supplied field objects
		\param ... Field objects (XCField*)

		Takes any number of field objects as parameters.  Will do an "=" comparison on
		each field supplied.  Expects parameters of type XCField*.

		Example: (find all locations for a given part number)
			query->AddWHERE( 1, Inv_Location->GetField( FLD_PART_NUMBER ) );
	*/
	void AddWHERE( long parmcount = 0, ... );  // NOTE: expects XCField* parameters!

	/**
		\brief Add a where clause to an sql statement given any number of Field Numbers within the buffer
		\param parmcount Number of supplied field numbers
		\param ... Supplied field numbers

		Takes any number of field numbers as parameters.  Will use the corresponding fields in
		the supplied buffer an perform an "=" comparison on each of them.  
		Expect parameters of type long int.

		Example: (find all locations for a given part number)
			query->AddWHEREByNum( 1, FLD_PART_NUMBER );
	*/
	void AddWHEREByNum( long parmcount = 0, ... );	//NOTE: expect long int parameters!

	/** 
		\brief Add a where clause to an sql statement given a field and optionally an XCCompare object
		\param field Source field
		\param comp Comparison object

		Takes a single XCField and XCCompare object as input.
		If no XCCompare object is supplied, the default ("=", "AND") is used

		Example: (find all locations for a given part number)
			XCCompare comp;
			query->AddWHERE( Inv_Location->GetField( FLD_PART_NUMBER ), &comp );
	*/
	void AddWHERE( XCField* field, XCCompare* comp = NULL );

	/**
		\brief Add a where clause to an sql statement given an XCCompare object
		\param compare Comparison object

		Takes a single XCCompare object as input.  This object must have its field variable set.

		Example: (find all location less than a given part number)
			XCCompare comp;
			comp.comparison = cmp_lessthan;
			comp.field = Inv_Location->GetField( FLD_PART_NUMBER);
			query->AddWHERE( &comp );
	*/
	void AddWHERE( XCCompare* compare );

	/// add a where clause to an sql statement
	void AddWHERE( XCField**, XCCompare* comp, long count = -1);

	/// Add an order-by clause to an sql statement given a field number and XCOrder object
	void AddORDERBY( long field, XCOrder* ord );

	/// Add an order-by clause to an sql statement given a null-terminated (or counted) list of field numbers and an array of XCCOrder objects
	void AddORDERBY( long* field, XCOrder* ord, int count = -1 );

	/// Add an order-by clause to an sql statement given an array of XCOrder objects (with the field member already set)
	void AddORDERBY( XCOrder* ord, int count = 1 );

	/**
		\brief Add an extra table to an sql statement via a join
		\param join XCJoin object
		\param FieldCount Number of fields to select from joined table
		\param ... XCField pointers to fields to select

		Accepts a single XCJoin object, the number of fields to include, and
		a pointer to each of those fields.  If the FieldCount is 0 (default).  All fields in the table will be
		included.

		Expect XCField* values in the parameters.
	*/	
	void AddJOIN( XCJoin* join, int FieldCount = 0, ... );

	/**
		\brief Add an extra table to an sql statement via a join
		
		Add a join just like AddJOIN, except expects long int values in the parameters which are field numbers
	*/
	void AddJOINByNum( XCJoin* join, int FieldCount = 0, ... );

	// Access functions so non-friend classes can retrieve protected variables
	// mostly for the XCSQLStatement* driver classes so we don't have to make them
	// all friends. (not documented)
	XCAppInterface* GetApp() { return app; };
	XCBuffer* GetBuffer() { return buffer; };
	sql_stmt_type GetType() const { return type; };
	XCCompare** GetCompares() { return compares; };
	bool GetDistinct() const { return distinct; };
	XCField* GetField( int i ) const { return fields[i]; };
	long GetFieldCount() const { return FieldCount; };
	long GetJoinCount() const { return JoinCount; };
	char *ViewData() { strcpy( CantView, "Can't view."); return CantView; };

protected:

	char CantView[20];

	//
	// Internal use functions - not documented
	//
	int EnlargeFieldList( int newsize );
	int EnlargeCompareList( int newsize );
	int EnlargeOrderList( int newsize );
	int EnlargeJoinList( int newsize );
	long GetDatabaseNbr();
	
	XCAppInterface* app;

	sql_stmt_type type;
	XCBuffer* buffer;		// the buffer we are selecting fields from (or the first of many)
	XCField** fields;		// array of pointers to the fields that we are selecting, 
							// if this is NULL, uses all fields in the buffer(s)
	int FieldCount;			// how many fields we have, 0 if using all fields in buffers
	XCCompare** compares;	// An array of pointers to XCCompares for the WHERE clause
							// NOTE: we own the XCCompare classes, but nothing else, everything else 
							//		 we just point to this was needed because the application doesn't 
							//		 always own a compare class to point to
	int CompareCount;		// how many compares we have
	XCOrder** orders;		// An array of points to XCOrders to order by
	int OrderCount;			// how many fields we are ordering by
	XCJoin** joins;			// array of pointers to XCJoins for extra tables
	int JoinCount;			// how many extra tables

	XCField* group;			// what field to group by, if any

	bool distinct;			// whether a SELECT statement should include DISTINCT or not

	long limit;				// Limit on the number of rows to retrieve in a select statement
};



#define LOCK_LIST_START() {
#define LOCK_TABLE( buffer ) { buffer, NULL }
#define LOCK_FIELD( buffer, field ) { buffer, field }
#define LOCK_LIST_END() }

struct XCLockInfo {
	XCBuffer* buffer;
	XCField* field;
};



// XCTxn Class
//////////////////////////////////////////////////////////////////////////////////

/// Manages database transaction logic
class __EXPORT XCTxn : public XCBase
{
	friend class XCDBHandler;

public:
	XCTxn( XCTxn* p, XCDBHandler* h );
	virtual ~XCTxn();

	/**
		\brief End a transaction
		\param commit Commit mode

		If commit is true, the transaction is commited, otherwise, the transaction
		will be rolled back
	*/
	void EndTxn( bool commit );

	/// Submit preformed sql statement
//	XCDBStatement* ExecuteSQL( XCSQLGenOld* sqlstr, exec_parm parm = SQL_NO_PARM );

	XCDBStatement* ExecuteSQL( XCSQLGen* query, exec_parm parm = SQL_NO_PARM );
	XCDBStatement* ExecuteSQL( XCPrepSQLGen* query, exec_parm parm = SQL_NO_PARM );

private:
	void LockTables( int count, XCLockInfo* lock, bool conditionally = true );
#ifdef TXNVALID
	bool GetValidTxn();
	void SetValidTxn( bool valid );
#endif

	void IncrementCounter( int count );
	XCDBTransaction* UseDatabase( long dbnum, bool start_txn = true, isolevel level = serializable);
	XCDBHandler* handler;
	XCTxn* parent;
	int txncount;

#ifdef TXNVALID
	bool validtxn;
#endif

	// database transaction array
	XCDBTransaction** txnarray;
	long txnarraycount;

	isolevel isolation;
};



//////////////////////////////////////////////////////////////////////////////////
// XCDBHandler Class
class __EXPORT XCDBHandler : public XCBase 
{
	friend class XCPrepSQLGen;
	friend class XCTxn;
	friend class XCLoadAppdic;
	friend class XCReload;
	friend class XCAppInterface;

public:
	/// Constructors
	XCDBHandler();
	/// Destructor
	virtual ~XCDBHandler();

	/// Create a new database transaction
	XCTxn* CreateTxn( XCTxn* parent = NULL );

	/// Connect to database
	XCDBConnection* Connect( long dbnbr );

	/// Refresh connections
	void Refresh();

private:
	// Initialize database with application handler
	int Initialize( XCAppInterface* a ); 

	// Returns the DBHandler record for a database number
	XCDBHandlerConnection* GetHandlerConnection( long dbnbr );

	// Returns a pointer to a DB Interface
	XCDBInterface* GetInterface( long dbnbr );

	// Returns a pointer to a DB Connection
	XCDBConnection* GetConnection( long dbnbr );

	// Maintains a list of open DB Interfaces
	XCDBHandlerConnection* dbarray;
	long dbarraycount;

	// ptr to assoc. AppHandler object
	XCAppInterface* appi;
};




////////////////////////////////////////////////////////////////////////////////////


#endif
